package fy.input.sql;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.json.JSONObject;

import fy.config.DBConfigBean;
import fy.util.DBUtil;

public final class QueryDao {
	private static Log log = LogFactory.getLog(QueryDao.class);
	private String[] args;
	private String result;
	private SQLBean sqlBean;

	private String ERROR_SQL_IS_NULL = "{success : false, msg : '参数错误，请联系OA系统管理员。'}";

	private DBConfigBean dbConfigBean;

	/**
	 * DRIVERCLASSNAME, URL, USERNAME, PASSWORD
	 * 
	 * @param dataSetArray
	 */
	public void setDBConfigBean(DBConfigBean dbConfigBean) {
		this.dbConfigBean = dbConfigBean;
	}

	public String getResult() {
		return result;
	}

	public void setArgs(String[] args) {
		this.args = args;
	}

	public void setSQLBean(String name) {
		this.sqlBean = SQLReader.getSQL(name);
	}

	public String getDatabase() {
		return this.sqlBean.getDatabase();
	}

	public void query() {
		if (sqlBean == null) {
			result = ERROR_SQL_IS_NULL;
		} else {
			log.info(new StringBuilder().append("QueryDao_HR SQL语句:").append(
					sqlBean.getStr()));
			Connection conn = null;
			PreparedStatement pst = null;
			ResultSet rs = null;

			try {
				conn = DBUtil.connect(dbConfigBean);
				List<String> strList = sqlBean.getStr();
				JSONObject jo = new JSONObject();
				int count = 0;
				for (int k = 0, size = strList.size(); k < size; k++) {
					pst = conn.prepareStatement(strList.get(k));
					if (args != null) {
						for (int i = 0, length = args.length; i < length; i++) {
							pst.setString(i + 1, args[i]);
						}
					}

					rs = pst.executeQuery();

					JSONObject child = new JSONObject();
					while (rs.next()) {
						JSONObject row = new JSONObject();
						ResultSetMetaData md = rs.getMetaData();
						String name = null;
						for (int i = 0, length = md.getColumnCount(); i < length; i++) {
							name = md.getColumnName(i + 1);
							row.put(name, rs.getString(i + 1));
							name = null;
						}
						md = null;
						name = null;
						child.append("rows", row);
						row = null;
						count++;
					}
					jo.append("children", child);
				}
				if (count > 0) {
					jo.put("success", true);
					result = jo.toString();
				} else {
					result = sqlBean.getIsNull();
				}
			} catch (Exception e) {
				result = sqlBean.getError();
				log.error(
						new StringBuilder().append("发生异常 SQL语句:").append(
								sqlBean.getStr()), e);
				e.printStackTrace();
			} finally {
				DBUtil.close(rs);
				DBUtil.close(pst);
				DBUtil.close(conn);
			}
		}
		sqlBean = null;
	}
        public void query_expExcel() {
		if (sqlBean == null) {
			result = ERROR_SQL_IS_NULL;
		} else {
			log.info(new StringBuilder().append("QueryDao_HR SQL语句:").append(
					sqlBean.getStr()));
			Connection conn = null;
			PreparedStatement pst = null;
			ResultSet rs = null;

			try {
				conn = DBUtil.connect(dbConfigBean);
				List<String> strList = sqlBean.getStr();
				JSONObject jo = new JSONObject();
				int count = 0;
				for (int k = 0, size = strList.size(); k < size; k++) {
					pst = conn.prepareStatement(strList.get(k));
					if (args != null) {
						for (int i = 0, length = args.length; i < length; i++) {
							pst.setString(i + 1, args[i]);
						}
					}

					rs = pst.executeQuery();

					JSONObject child = new JSONObject();
					while (rs.next()) {
						JSONObject row = new JSONObject();
						ResultSetMetaData md = rs.getMetaData();
						String name = null;
						for (int i = 0, length = md.getColumnCount(); i < length; i++) {
							name = md.getColumnName(i + 1);
							row.put(name, rs.getString(i + 1));
							name = null;
						}
						md = null;
						name = null;
						child.append("rows", row);
						row = null;
						count++;
					}
					jo.append("children", child);
				}
				if (count > 0) {
					jo.put("success", true);
					result = jo.toString();
				} else {
					result = sqlBean.getIsNull();
				}
			} catch (Exception e) {
				result = sqlBean.getError();
				log.error(
						new StringBuilder().append("发生异常 SQL语句:").append(
								sqlBean.getStr()), e);
				e.printStackTrace();
			} finally {
				DBUtil.close(rs);
				DBUtil.close(pst);
				DBUtil.close(conn);
			}
		}
		sqlBean = null;
	}
}
